---
title: Create constraint
description: A create constraint operation adds a new constraint to an existing table.
---

## Structure

`UNIQUE`, `CHECK`, `PRIMARY KEY` and `FOREIGN KEY` constraints are supported.

Required fields: `name`, `table`, `type`, `up`, `down`.

<YamlJsonTabs>
```yaml
create_constraint:
  table: name of table
  name: my_unique_constraint
  columns: [column1, column2]
  type: unique | check | primary_key | foreign_key
  check: SQL expression for CHECK constraint
  no_inherit: true|false
  references:
    name: name of foreign key reference
    table: name of referenced table
    columns: [names of referenced columns]
    on_delete: ON DELETE behaviour, can be CASCADE, SET NULL, RESTRICT, or NO ACTION. Default is NO ACTION
    on_delete_set_columns: [list of FKs to set, in on delete operation on SET NULL or SET DEFAULT]
    on_update: ON UPDATE behaviour, can be CASCADE, SET NULL, RESTRICT, or NO ACTION. Default is NO ACTION
    match_type: match type, can be SIMPLE or FULL. Default is SIMPLE
  up:
    column1: up SQL expressions for each column covered by the constraint
    ...
  down:
    column1: up SQL expressions for each column covered by the constraint
    ...
```
```json
{
  "create_constraint": {
    "table": "name of table",
    "name": "my_unique_constraint",
    "columns": ["column1", "column2"],
    "type": "unique"| "check" | "primary_key"| "foreign_key",
    "check": "SQL expression for CHECK constraint",
    "no_inherit": "true|false",
    "references": {
      "name": "name of foreign key reference",
      "table": "name of referenced table",
      "columns": "[names of referenced columns]",
      "on_delete": "ON DELETE behaviour, can be CASCADE, SET NULL, RESTRICT, or NO ACTION. Default is NO ACTION",
      "on_delete_set_columns": ["list of FKs to set", "in on delete operation on SET NULL or SET DEFAULT"],
      "on_update": "ON UPDATE behaviour, can be CASCADE, SET NULL, RESTRICT, or NO ACTION. Default is NO ACTION",
      "match_type": "match type, can be SIMPLE or FULL. Default is SIMPLE"
    },
    "up": {
      "column1": "up SQL expressions for each column covered by the constraint",
      ...
    },
    "down": {
      "column1": "up SQL expressions for each column covered by the constraint",
      ...
    }
  }
}
```
</YamlJsonTabs>

An `up` and `down` SQL expression is required for each column covered by the constraint, and no other column names are permitted. For example, when adding a new constraint covering columns `a` and `b` the `up` and `down` fields should look like:

<YamlJsonTabs>
```yaml
up:
  a: up SQL expression for column a
  b: up SQL expression for column b
down:
  a: down SQL expression for column a
  b: down SQL expression for column b
```
```json
{
  "up": {
    "a": "up SQL expression for column a",
    "b": "up SQL expression for column b",
  },
  "down": {
    "a": "down SQL expression for column a",
    "b": "down SQL expression for column b",
  }
}
```
</YamlJsonTabs>

## Examples

### Add a `UNIQUE` constraint

Add a multi-column unique constraint on the `tickets` table:

<ExampleSnippet example="44_add_table_unique_constraint.yaml" languange="yaml" />

### Add a `CHECK` constraint

Add a check constraint on the `sellers_name` and `sellers_zip` fields on the `ticket` table. The `up` SQL expression ensures that pairs of values not meeting the new constraint on the old columns are data migrated to values that meet the new constraint in the new columns:

<ExampleSnippet example="45_add_table_check_constraint.yaml" languange="yaml" />

### Add a `PRIMARY KEY` constraint

Add a primary key constraint to the the `tasks` table:

<ExampleSnippet
  example="55_add_primary_key_constraint_to_table.yaml"
  languange="yaml"
/>

### Add a `FOREIGN KEY` constraint

Add a multi-column foreign key constraint to the the `tickets` table. The `up` SQL expressions here don't do any data transformation:

<ExampleSnippet
  example="47_add_table_foreign_key_constraint.yaml"
  languange="yaml"
/>
